Custom Tables

Custom Tables are user defined database tables that can be used in one of two ways:

  • Local Only – Table is used on mobile device only and is not linked to an actual data source.

  • Remote ADO – Data will be downloaded from a remote ADO database, but the database columns and SQL query are user-defined.

  • Data can be downloaded from a remote database data source to a Custom Table but you cannot update a remote data source directly from a Custom Table. To upload data to a remote data source, you must first copy the data from the Custom Table into another type of Digitise Apps Data Source, using the SelectLocal Scripting Method.

 

You can create a Custom Table in one of the following ways:

  • Using the New Data Source option and manually defining the columns in the table

  • Using the Create as Custom Table … option to copy an existing Data Source to a Custom Table.

 

To create a Custom Table:

Choose New Data Source from the Project group of the Ribbon's Home tab. This will display a sub-menu of the different data source types.

Choose Custom Table.

Alternatively, right-click on an existing Data Source and choose Create as Custom Table… from the menu displayed.

 

The New Custom Table dialog box will be displayed.

If you used the Create as Custom Table option, this dialog will include a column in the new Table for each data item in the original Data Source. You can add additional data items to the Table or delete items from the original Data Source that you don't want in this Custom Table should you want to. You can do this in the same way as described below for a new table.

If you used the New Data Source option, the Table Columns section will be empty.

 

Enter a name for this Data Source in the Table Name field at the bottom of the dialog box.

To enter a new column to your custom table, click on the Add button below the Columns list. A new column will be added to the list and you can then edit the values for it by clicking in each table cell in turn that you want to edit.

To delete a column from the Table, select the column in the Columns table and then choose the Remove button.

When you have finished creating your Custom Table, click on the OK button.

 

Your new table will now be listed in the Data Sources Pane under Custom Tables – click on the Data Sources tab to display this pane if it isn't already in view. The Table will display in the tree view which you can expand and contract in the usual way.

Each node in the tree view has its own set of Properties. To display the Properties for a particular item, double-click on its node in the tree view. The Properties will then be displayed in the Properties Pane.

By default, a new Table is set to act as a Local Only table. If you want to define your own columns and SQL query, you will need to change the configuration to specify Remote ADO mode instead. This is done by editing the Mode Property for the Table.

Double-click on the Table Name in the Data Sources Pane to display the Table Properties. Click on the Mode Property value and choose the required setting from the drop-down list.

If you create a Custom Table from an existing Data Source, the Mode property will automatically be set to Remote ADO.

You may want to edit one or more of the other Properties as well.

The Table Properties available are:

Name

The name you have given to refer to this Data Source in the Digitise Apps Client Database.

 

Storage Mode

Select Disk to retain data in the Client Database, Memory for temporary data. On the Android, Apple and Windows Universal Platforms, data is always stored on 'disk'.

 

Mode

Select Local Only if table is for local client use only, Remote ADO to link it to an ADO database. Selecting the latter will display further Properties (see below).

 

Device Database

Allows you to specify the local database on the mobile device in which you want your app to store data records for this Data Source. Leave at Default to use the default database to store this data.

Specify the name of the database file to use without the file extension. Note that for the Android, Apple and Windows Universal Platforms, you cannot include a path with the file name.

 

Timeout

This Property is only available if you have set the Mode Property above to Remote ADO.

It specifies the number of seconds the Digitise Apps Client will wait for data to start being downloaded when a data download is requested.

 

Connection String

This Property is only available if you have set the Mode Property above to Remote ADO.

It specifies the Connection String required to connect to the remote ADO Database. Note, if you are not using Windows authentication to log in to the remote database, the username and password will be stored in the connection string in plain, unencrypted text.

 

Username and Password

These Properties are only available if you have set the Mode Property above to Remote ADO.

You can use them to allow App Server to impersonate a specific Windows user when using the remote database. Note the password is displayed as asterisks and the values are stored in an encrypted format.

 

SQL Statement

This Property is only available if you have set the Mode Property above to Remote ADO.

It specifies a SQL Select statement used to retrieve required data from the remote database, e.g. "SELECT Name, Telephone FROM Customer WHERE CustomerCode = 1". You may want to specify the order in which records should be returned by including an ORDER BY clause, e.g. "SELECT * from table1 WHERE user = 'John' ORDER BY table1.userID". You can enter any valid sql statement here.

 

Sync Direction

This Property is only available if you have set the Mode Property above to Remote ADO.

It allows you to specify default Database Data Sources and Custom Tables to be transferred when using the Synchronise Scripting Method to transfer data between the mobile device and the back office.

If you do not explicitly specify the Data Sources to be transferred when calling Synchronise, the Method will check the value of this Property for all Database Data Sources and Custom Tables in order to determine which to include in the synchronisation.

The Property can take one of the following values:

None

This Data Source will not be synchronised.

This value will automatically be set in Custom Table data items for existing Projects which were created using Digitise Apps' predecessor MX with a version of MX prior to v10.

 

Down

This Data Source will be included in the sync but data will only be downloaded from the back office to the mobile device.

This is the default value for new Projects or projects being upgraded from the earlier MX v10 onwards.

 

Client-Side Conflict Resolution

This Property is only available if you have set the Mode Property above to Remote ADO.

It is only relevant when using the Synchronise Scripting Method and determines how data downloaded to a device updates this local database table.

If this Property is set to Server, the local table will be deleted and replaced with the data being downloaded.

If this Property is set to Client, any records in the local table marked as modified will not be overwritten or updated with data from the server. All other records will be updated with the downloaded data including deleting any records from the local table which are no longer present in the downloaded data and creating new local records for new records in the download.

If this Custom Table doesn't have a Primary Key, this Property is ignored and the local table will be deleted and replaced with the downloaded data.

SQL Type

Specifies the SQL data type to be used for this data item in the local database on the mobile device.

 

Default Value

Default value for this data item.

 

Key Field

Set this Property to True if this data item is a Key field or part of a combination of columns making up a Key for this Data Source. A Key field or combination of Key fields must contain a unique value for each record.

Otherwise it should be set to False.

 

Allow Null

If this data item can contain Null values, this Property should be set to True, otherwise it should be set to False.

If the value of the Mode Property (see above under Data Source Nodes) is set to Remote ADO, the value of this Property should match the equivalent property for the relevant column in the remote Target data source otherwise you may get errors when downloading data to this data item.

 

Ignore Invalid Control Characters

If set to True, any unrecognised control characters will be ignored when displayed on your mobile devices.

 

Auto

Allows you to automatically generate unique values for a data column when a record is created using the CreateRecord Method. You can use this option to populate an Identity field or generate a unique GUID value.

The default value is None, which means this data item will not be automatically assigned a value when the record is created.

Select GUID to enter a unique GUID value, in the form:

00000000-0000-0000-0000-000000000000

Note that the data column must have a GUID or string data type.

Select Identity to enter a unique Identity value. Selecting Identity displays two further Properties:

Identity Seed

Provides the starting numeric value.

 

Identity Increment

Specifies the amount to increment or decrease the Seed or last used value to generate the number when a new record is created.

 

Note that GUID and Identity values are only guaranteed to be unique within the local database table.

  • When using Remote ADO mode for a Custom Table:

    • The order of columns output by the Select statement should match the order defined in the Custom Table.

    • There is no data-type checking, so ensure that the data types for the defined columns match those in the actual database.

    • Custom Tables in this mode can only be used for downloading data; data cannot currently be uploaded from them.

    • You can also specify the SQL Select statement or override a Select statement specified in the SQL Statement Property at runtime from within your Scripts, for example using the SelectLocal and SetSelectString Scripting Methods.

    • Select strings for use with Custom Tables can start with SELECT or SELECT DISTINCT.

    • Within a Select string, columns can be referred to using the format <table>.<column> but <table>.* is not supported.

    • You can use SQL stored procedures to download data to a Custom Table where more complex processing is required than is available using a standard SELECT statement. Stored procedures may allow you to move data processing from the device to a more powerful SQL server machine improving the efficiency of your apps. For more information about using stored procedures refer to the Knowledge Centre post in the Community Portal Area of our web site:

      Community Portal → Knowledge Centre→ Hints, Tips and FAQs → MX - Using SQL Stored Procedures in MX

      Note that whilst this Knowledge Centre post refers to Digitise Apps' predecessor MX, the contents applies equally to Digitise Apps.

    • When using a Custom Table within a Script, table names are referenced in the Script with the prefix Custom, e.g. Custom.MyCustomTable